package com.example.demo.excel;

import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import com.example.demo.entity.*;
import com.example.demo.repository.*;
import org.apache.poi.ss.usermodel.Workbook;
import org.springframework.beans.BeanUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.multipart.MultipartFile;

import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.ExcelImportUtil;
import cn.afterturn.easypoi.excel.entity.ExportParams;
import cn.afterturn.easypoi.excel.entity.ImportParams;

@RestController
@RequestMapping(value = "/Excel")
public class ExcelOutController {

	private DateFormat dateFormat1 = new SimpleDateFormat("yyyy-MM-dd ");
	@Autowired
	private TestExcelRepository ghqDAO;
	@Autowired
	private NewCar2Repository NewCarDAO;
	@Autowired
	private NewCar1Repository NewCar1DAO;
	@Autowired
	private SaleBoutique1Repository SaleBoutique1DAO;
	@Autowired
	private SaleBoutique2Repository SaleBoutique2DAO;
	@Autowired
	private CarKeep2Repository CarKeepRepository2DAO;
	@Autowired
	private  CarKeepRepository CarKeepRepository1DAO;
	@Autowired
	private  CarNumberCompanyRepository1 CarNumberCompanyRepository1DAO;
	@Autowired
	private  CarNumberCompanyRepository2 carNumberCompanyRepository2DAO;
	@Autowired
	private InsuranceRepository InsuranceRepository1DAO;
	@Autowired
	private Insurance2Repository Insurance2Repository2DAO;
	@Autowired
	private MoneyCompanyRepository MoneyCompanyRepository1DAO;
	@Autowired
	private MoneyCompany2Repository MoneyCompany2Repository2DAO;
	@Autowired
	private XiMeiRepository XiMeiRepository1DAO;
	@Autowired
	private XiMei2Repository XiMei2Repository2DAO;
	@Autowired
	private YanBaoRepository YanBaoRepository1DAO;
	@Autowired
	private YanBao2Repository YanBao2Repository2DAO;
	@Autowired
	private CarsOrder2Repository CarsOrder2Repository2DAO;
	@Autowired
	private CarsOrderRepository CarsOrderRepository1DAO;


		public List<ExcelUser> getExcelUser(){
		List<ExcelUser> users = new ArrayList<>();
		for (int i = 0; i < 5; i++) {
			ExcelUser user = new ExcelUser();
			user.setId(String.valueOf(i));
			user.setName("张三"+i);
			user.setAge(10+i);
			user.setBir(new Date());
			users.add(user);
			Card card = new Card();
			card.setNo("11111111111111123");
			card.setAdd("是的撒啥饭是的撒啥的");
			user.setCard(card);
			//订单信息
			List<Orders> orders = new ArrayList<>();
			orders.add(new Orders("123","123"));
			orders.add(new Orders("12","123"));
			orders.add(new Orders("13","123"));
			user.setOrders(orders);
			Orders a = new Orders();
		}
		return users;
	}
	@RequestMapping(value = "/CarOderOut")
	public void out() {
		//车型
		List<NewCar1> allNewCar = NewCar1DAO.findAll();
		List<String> dataList = new ArrayList<>();
		for (NewCar1 newCar2 : allNewCar) {
			dataList.add(newCar2.getName());
		}
		dataList.forEach(System.out::println);
		//精品
		List<SaleBoutique1> saleBoutique1DAOAll = SaleBoutique1DAO.findAll();
		List<String> dataList1 = new ArrayList<>();
		for (SaleBoutique1 saleBoutique1 : saleBoutique1DAOAll) {
			dataList1.add(saleBoutique1.getName());
		}
		dataList1.forEach(System.out::println);
		//保养套餐
		List<CarKeep> carKeepRepository1DAOAll = CarKeepRepository1DAO.findAll();
		List<String> dataList2 = new ArrayList<>();
		for (CarKeep carKeep : carKeepRepository1DAOAll) {
			dataList2.add(carKeep.getName());
		}
		dataList2.forEach(System.out::println);
		//延保
		List<YanBao> all1 = YanBaoRepository1DAO.findAll();
		List<String> dataList3 = new ArrayList<>();
		for (YanBao yanBao : all1) {
			dataList3.add(yanBao.getName());
		}
		dataList3.forEach(System.out::println);
		//洗美
		List<XiMei> xiMeiRepository1DAOAll = XiMeiRepository1DAO.findAll();
		List<String> dataList4 = new ArrayList<>();
		for (XiMei xiMei : xiMeiRepository1DAOAll) {
			dataList4.add(xiMei.getName());
		}
		dataList4.forEach(System.out::println);
		//保险公司
		List<Insurance> insuranceRepository1DAOAll = InsuranceRepository1DAO.findAll();
		List<String> dataList5 = new ArrayList<>();
		for (Insurance insurance : insuranceRepository1DAOAll) {
			dataList5.add(insurance.getName());
		}
		dataList5.forEach(System.out::println);
		//金融公司
		List<MoneyCompany> moneyCompanyRepository1DAOAll = MoneyCompanyRepository1DAO.findAll();
		List<String> dataList6 = new ArrayList<>();
		for (MoneyCompany moneyCompany : moneyCompanyRepository1DAOAll) {
			dataList6.add(moneyCompany.getName());
		}
		//车牌公司
		List<CarNumberCompany1> all = CarNumberCompanyRepository1DAO.findAll();
		List<String> dataList7 = new ArrayList<>();
		for (CarNumberCompany1 carNumberCompany1 : all) {
			dataList7.add(carNumberCompany1.getName());
		}


		//获得数据
		List<CarOrderExcel> users = new ArrayList<>();
		CarOrderExcel aaa  = new CarOrderExcel();
		aaa.setColor("红色");
		users.add(aaa);
		//导出excel                                                       表名，sheet名                     想导出的表名    数据
		Workbook workbook = ExcelExportUtil.exportExcel(new ExportParams("订单录入","订单录入"), CarOrderExcel.class,users);
		ExcelUtil.selectList(workbook, 2, 65535, 15, 15, dataList.toArray(new String[dataList.size()]), 1);
		ExcelUtil.selectList(workbook, 2, 65535, 20, 20, dataList1.toArray(new String[dataList.size()]), 2);
		ExcelUtil.selectList(workbook, 2, 65535, 21, 21, dataList2.toArray(new String[dataList.size()]), 3);
		ExcelUtil.selectList(workbook, 2, 65535, 22, 22, dataList3.toArray(new String[dataList.size()]), 4);
		ExcelUtil.selectList(workbook, 2, 65535, 23, 23, dataList4.toArray(new String[dataList.size()]), 5);
		ExcelUtil.selectList(workbook, 2, 65535, 24, 24, dataList5.toArray(new String[dataList.size()]), 6);
		ExcelUtil.selectList(workbook, 2, 65535, 27, 27, dataList6.toArray(new String[dataList.size()]), 7);
		ExcelUtil.selectList(workbook, 2, 65535, 28, 28, dataList7.toArray(new String[dataList.size()]), 8);
		ExcelUtil.selectList(workbook, 2, 65535, 29, 29, dataList1.toArray(new String[dataList.size()]), 8);
		ExcelUtil.selectList(workbook, 2, 65535, 30, 30, dataList2.toArray(new String[dataList.size()]), 8);
		ExcelUtil.selectList(workbook, 2, 65535, 31, 31, dataList3.toArray(new String[dataList.size()]), 8);
		ExcelUtil.selectList(workbook, 2, 65535, 32, 32, dataList4.toArray(new String[dataList.size()]), 8);
		ExcelUtil.selectList(workbook, 2, 65535, 33, 33, dataList5.toArray(new String[dataList.size()]), 8);



		//写入指定位置
		//获得一个流
		FileOutputStream outt;
		try {
			//流写入的位置
			outt = new FileOutputStream("c://carOder.xls");
			workbook.write(outt);
			//关流
			outt.close();
			workbook.close();
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
}
	}

	//新车车型录入
	@RequestMapping(value = "/newCarIn")
	public void newCarIn(MultipartFile inFile) throws Exception {
		ImportParams params = new ImportParams();
		params.setTitleRows(1);//标题列站几行
		params.setHeadRows(1);//header列站几行
		List<NewCar> testExcel = ExcelImportUtil.importExcel(inFile.getInputStream(), NewCar.class, params);
		testExcel.forEach(System.out::println);
		for (NewCar newCar : testExcel) {
			NewCar2 newCar2 = new NewCar2();
			BeanUtils.copyProperties(newCar,newCar2,"name");
			newCar2.setXiuGaiDate(newCar.getDate());
			NewCar1 newCar1 = new NewCar1();
			newCar1.setName(newCar.getName());
			newCar2.setNewCar(newCar1);
			NewCar1DAO.save(newCar1);
			NewCarDAO.save(newCar2);
		}
	}

	//新车车型录入
	@RequestMapping(value = "/SaleBoutiqueExcelIn")
	public void SaleBoutiqueExcelIn(MultipartFile inFile) throws Exception {
		ImportParams params = new ImportParams();
		params.setTitleRows(3);//标题列站几行
		params.setHeadRows(2);//header列站几行
		List<SaleBoutiqueExcel> testExcel = ExcelImportUtil.importExcel(inFile.getInputStream(), SaleBoutiqueExcel.class, params);
		testExcel.forEach(System.out::println);
		for (SaleBoutiqueExcel newCar : testExcel) {
			SaleBoutique2 newCar2 = new SaleBoutique2();
			BeanUtils.copyProperties(newCar,newCar2,"name");
			newCar2.setStartDays(new Date());
			SaleBoutique1 newCar1 = new SaleBoutique1();
			newCar1.setName(newCar.getName());
			newCar2.setSaleBoutique(newCar1);
			SaleBoutique1DAO.save(newCar1);
			SaleBoutique2DAO.save(newCar2);
		}
	}

	//保养套餐
	@RequestMapping(value = "/CarKeepExcelIn")
	public void CarKeepExcelIn(MultipartFile inFile) throws Exception {
		ImportParams params = new ImportParams();
		params.setTitleRows(3);//标题列站几行
		params.setHeadRows(2);//header列站几行
		List<CarKeepExcel> testExcel = ExcelImportUtil.importExcel(inFile.getInputStream(), CarKeepExcel.class, params);
		testExcel.forEach(System.out::println);
		for (CarKeepExcel newCar : testExcel) {
			CarKeep2 newCar2 = new CarKeep2();
			BeanUtils.copyProperties(newCar,newCar2,"name");
			CarKeep newCar1 = new CarKeep();
			newCar1.setName(newCar.getName());
			newCar2.setCarKeep(newCar1);
			newCar2.setBeginDate(new Date());
			CarKeepRepository1DAO.save(newCar1);
			CarKeepRepository2DAO.save(newCar2);
		}
	}

	//车牌公司
	@RequestMapping(value = "/CarNumberCompanyExcelIn")
	public void CarNumberCompanyExcelIn(MultipartFile inFile) throws Exception {
		ImportParams params = new ImportParams();
		params.setTitleRows(3);//标题列站几行
		params.setHeadRows(2);//header列站几行
		List<CarNumberCompanyExcel> testExcel = ExcelImportUtil.importExcel(inFile.getInputStream(), CarNumberCompanyExcel.class, params);
		testExcel.forEach(System.out::println);
		for (CarNumberCompanyExcel newCar : testExcel) {
			CarNumberCompany2 newCar2 = new CarNumberCompany2();
			BeanUtils.copyProperties(newCar,newCar2,"name");
			CarNumberCompany1 newCar1 = new CarNumberCompany1();
			newCar1.setName(newCar.getName());
			newCar2.setCarNumberCompany(newCar1);
			newCar2.setBeginDate(new Date());
			CarNumberCompanyRepository1DAO.save(newCar1);
			carNumberCompanyRepository2DAO.save(newCar2);
		}
	}
	//保险公司
	@RequestMapping(value = "/InsuranceExcelIn")
	public void InsuranceExcelIn(MultipartFile inFile) throws Exception {
		ImportParams params = new ImportParams();
		params.setTitleRows(3);//标题列站几行
		params.setHeadRows(2);//header列站几行
		List<InsuranceExcel> testExcel = ExcelImportUtil.importExcel(inFile.getInputStream(), InsuranceExcel.class, params);
//		testExcel.forEach(System.out::println);
		for (InsuranceExcel newCar : testExcel) {
			Insurance2 newCar2 = new Insurance2();
			BeanUtils.copyProperties(newCar,newCar2,"name");
			Insurance newCar1 = new Insurance();
			newCar1.setName(newCar.getName());
			newCar2.setInsurance(newCar1);
			newCar2.setBeginDate(new Date());
			InsuranceRepository1DAO.save(newCar1);
			Insurance2Repository2DAO.save(newCar2);
		}
	}

	//金融公司
	@RequestMapping(value = "/MoneyCompanyExcelIn")
	public void MoneyCompanyExcelIn(MultipartFile inFile) throws Exception {
		ImportParams params = new ImportParams();
		params.setTitleRows(3);//标题列站几行
		params.setHeadRows(2);//header列站几行
		List<MoneyCompanyExcel> testExcel = ExcelImportUtil.importExcel(inFile.getInputStream(), MoneyCompanyExcel.class, params);
//		testExcel.forEach(System.out::println);
		for (MoneyCompanyExcel newCar : testExcel) {
			MoneyCompany2 newCar2 = new MoneyCompany2();
			BeanUtils.copyProperties(newCar,newCar2,"name");
			MoneyCompany newCar1 = new MoneyCompany();
			newCar1.setName(newCar.getName());
			newCar2.setMoney(newCar1);
			newCar2.setBeginDate(new Date());
			MoneyCompanyRepository1DAO.save(newCar1);
			MoneyCompany2Repository2DAO.save(newCar2);
		}
	}

	//洗美
	@RequestMapping(value = "/XiMeiExcelIn")
	public void XiMeiExcelIn(MultipartFile inFile) throws Exception {
		ImportParams params = new ImportParams();
		params.setTitleRows(3);//标题列站几行
		params.setHeadRows(2);//header列站几行
		List<XiMeiExcel> testExcel = ExcelImportUtil.importExcel(inFile.getInputStream(), XiMeiExcel.class, params);
		testExcel.forEach(System.out::println);
		for (XiMeiExcel newCar : testExcel) {
			XiMei2 newCar2 = new XiMei2();
			BeanUtils.copyProperties(newCar,newCar2,"name");
			XiMei newCar1 = new XiMei();
			newCar1.setName(newCar.getName());
			newCar2.setXiMei(newCar1);
			newCar2.setBeginDate(new Date());
			XiMeiRepository1DAO.save(newCar1);
			XiMei2Repository2DAO.save(newCar2);
		}
	}

	//洗美
	@RequestMapping(value = "/YanBaoExcelIn")
	public void YanBaoExcelIn(MultipartFile inFile) throws Exception {
		ImportParams params = new ImportParams();
		params.setTitleRows(3);//标题列站几行
		params.setHeadRows(2);//header列站几行
		List<YanBaoExcel> testExcel = ExcelImportUtil.importExcel(inFile.getInputStream(), YanBaoExcel.class, params);
		testExcel.forEach(System.out::println);
		for (YanBaoExcel newCar : testExcel) {
			YanBao2 newCar2 = new YanBao2();
			BeanUtils.copyProperties(newCar,newCar2,"name");
			YanBao newCar1 = new YanBao();
			newCar1.setName(newCar.getName());
			newCar2.setYanBao(newCar1);
			newCar2.setBeginDate(new Date());
			YanBaoRepository1DAO.save(newCar1);
			YanBao2Repository2DAO.save(newCar2);
		}
	}
	//车牌公司
	@RequestMapping(value = "/CarOrderExcelIn")
	public void CarOrderExcelIn(MultipartFile inFile) throws Exception {
		ImportParams params = new ImportParams();
		params.setTitleRows(3);//标题列站几行
		params.setHeadRows(2);//header列站几行
		List<CarOrderExcel> testExcel = ExcelImportUtil.importExcel(inFile.getInputStream(), CarOrderExcel.class, params);
		testExcel.forEach(System.out::println);
		for (CarOrderExcel newCar : testExcel) {
			CarsOrder2 newCar2 = new CarsOrder2();
			BeanUtils.copyProperties(newCar,newCar2,"name");
			CarsOrder newCar1 = new CarsOrder();
			newCar1.setName(newCar.getName());
			newCar2.setCarsorder(newCar1);
			newCar2.setBeginDate(new Date());
			CarsOrderRepository1DAO.save(newCar1);
			CarsOrder2Repository2DAO.save(newCar2);
		}
	}

}
